﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CoreProvince
{
    public class PlaceBll
    {
        public static int Insert(nlit_Place Info, string strConnect)
        {
            int id = 0;
            using (ProvinceToLinQDataContext db = new ProvinceToLinQDataContext(strConnect))
            {
                try
                {
                    db.nlit_Places.InsertOnSubmit(Info);
                    db.SubmitChanges();
                    id = Info.Id;
                }
                catch { id = 0; }
                finally { db.Dispose(); }
            }
            return id;
        }

        public static bool Update(nlit_Place Info, string strConnect)
        {
            bool res = false;
            using (ProvinceToLinQDataContext db = new ProvinceToLinQDataContext(strConnect))
            {
                try
                {
                    nlit_Place o = db.nlit_Places.Single(p => p.Id == Info.Id);
                    if (o != null)
                    {
                        o.Name = Info.Name;
                        o.Address = Info.Address;
                        o.Area = Info.Area;
                        o.AreaUnit = Info.AreaUnit;
                        o.CategoryId = Info.CategoryId;
                        o.DateBegin = Info.DateBegin;
                        o.DateEnd = Info.DateEnd;
                        o.DateUpdate = DateTime.Now;
                        o.Description = Info.Description;
                        o.HuongBanCong = Info.HuongBanCong;
                        o.HuongNha = Info.HuongNha;
                        o.Img = Info.Img;
                        o.ImgMap = Info.ImgMap;
                        o.LoiVao = Info.LoiVao;
                        o.MatTien = Info.MatTien;
                        o.NoiThat = Info.NoiThat;
                        o.Price = Info.Price;
                        o.PriceUnit = Info.PriceUnit;
                        o.ProjectId = Info.ProjectId;
                        o.ProvinceId = Info.ProvinceId;
                        o.SoPhong = Info.SoPhong;
                        o.SoTang = Info.SoTang;
                        o.SoTolet = Info.SoTolet;
                        o.TypeNewsId = Info.TypeNewsId;
                        o.UserAddress = Info.UserAddress;
                        o.UserEmail = Info.UserEmail;
                        o.UserName = Info.UserName;
                        o.UserPhone = Info.UserPhone;
                        o.UserTelephone = Info.UserTelephone;
                        o.WardId = Info.WardId;
                        
                        db.SubmitChanges();
                        res = true;
                    }
                }
                catch { }
                finally { db.Dispose(); }
            }
            return res;
        }

        public static bool UpdateOrderDisplay(int id, int orderDisplay, string strConnect)
        {
            bool res = false;
            using (ProvinceToLinQDataContext db = new ProvinceToLinQDataContext(strConnect))
            {
                try
                {
                    nlit_Place o = db.nlit_Places.Single(p => p.Id == id);
                    if (o != null)
                    {
                        o.OrderDisplay = orderDisplay;
                        db.SubmitChanges();
                        res = true;
                    }
                }
                catch { }
                finally { db.Dispose(); }
            }
            return res;
        }

        public static bool UpdateIsDeleted(int id, bool isDeleted, string strConnect)
        {
            bool res = false;
            using (ProvinceToLinQDataContext db = new ProvinceToLinQDataContext(strConnect))
            {
                try
                {
                    nlit_Place o = db.nlit_Places.Single(p => p.Id == id);
                    if (o != null)
                    {
                        o.IsDeleted = isDeleted;
                        db.SubmitChanges();
                        res = true;
                    }
                }
                catch { }
                finally { db.Dispose(); }
            }
            return res;
        }

        public static bool UpdateIsActive(int id, bool isActive, string strConnect)
        {
            bool res = false;
            using (ProvinceToLinQDataContext db = new ProvinceToLinQDataContext(strConnect))
            {
                try
                {
                    nlit_Place o = db.nlit_Places.Single(p => p.Id == id);
                    if (o != null)
                    {
                        o.IsActive = isActive;
                        db.SubmitChanges();
                        res = true;
                    }
                }
                catch { }
                finally { db.Dispose(); }
            }
            return res;
        }

        public static List<nlit_Place> GetList(bool isDeleted, bool isActive, string strConnect)
        {
            List<nlit_Place> List = new List<nlit_Place>();

            using (ProvinceToLinQDataContext db = new ProvinceToLinQDataContext(strConnect))
            {
                try
                {
                    var varlist = from p in db.nlit_Places
                                  orderby p.OrderDisplay
                                  where p.IsDeleted == isDeleted && p.IsActive == isActive
                                  select p;
                    List = varlist.ToList();
                }
                catch
                {
                    List = null;
                }
                finally
                {
                    db.Dispose();
                }
            }
            return List;
        }

        public static List<nlit_Place> GetList(string strConnect)
        {
            List<nlit_Place> List = new List<nlit_Place>();

            using (ProvinceToLinQDataContext db = new ProvinceToLinQDataContext(strConnect))
            {
                try
                {
                    var varlist = from p in db.nlit_Places
                                  where p.IsDeleted == false
                                  orderby p.OrderDisplay
                                  select p;
                    List = varlist.ToList();
                }
                catch
                {
                    List = null;
                }
                finally
                {
                    db.Dispose();
                }
            }
            return List;
        }

        public static List<nlit_Place> GetList(string email, string strConnect)
        {
            List<nlit_Place> List = new List<nlit_Place>();

            using (ProvinceToLinQDataContext db = new ProvinceToLinQDataContext(strConnect))
            {
                try
                {
                    var varlist = from p in db.nlit_Places
                                  where p.IsDeleted == false && p.IsActive==true && p.UserEmail.Equals(email)
                                  orderby p.DateCreate descending, p.OrderDisplay descending
                                  select p;
                    List = varlist.ToList();
                }
                catch
                {
                    List = null;
                }
                finally
                {
                    db.Dispose();
                }
            }
            return List;
        }

        public static List<VG_NhaDat> GetList(int nhadatId ,string strConnect)
        {
            List<VG_NhaDat> List = new List<VG_NhaDat>();

            using (ProvinceToLinQDataContext db = new ProvinceToLinQDataContext(strConnect))
            {
                try
                {
                    var varlist = from p in db.VG_NhaDats
                                  where p.IsDeleted == false && p.IsActive==true && p.NhaDatId == nhadatId
                                  orderby p.OrderDisplay
                                  select p;
                    List = varlist.ToList();
                }
                catch
                {
                    List = null;
                }
                finally
                {
                    db.Dispose();
                }
            }
            return List;
        }

        public static List<VG_NhaDat> GetListByLoaiNhaDat(int loaiNhaDatId, string strConnect)
        {
            List<VG_NhaDat> List = new List<VG_NhaDat>();

            using (ProvinceToLinQDataContext db = new ProvinceToLinQDataContext(strConnect))
            {
                try
                {
                    var varlist = from p in db.VG_NhaDats
                                  where p.IsDeleted == false && p.IsActive == true && p.LoaiNhaDatId == loaiNhaDatId
                                  orderby p.OrderDisplay
                                  select p;
                    List = varlist.ToList();
                }
                catch
                {
                    List = null;
                }
                finally
                {
                    db.Dispose();
                }
            }
            return List;
        }

        public static List<VG_NhaDat> GetListByNhaDat(int nhaDatId, string strConnect)
        {
            List<VG_NhaDat> List = new List<VG_NhaDat>();

            using (ProvinceToLinQDataContext db = new ProvinceToLinQDataContext(strConnect))
            {
                try
                {
                    var varlist = from p in db.VG_NhaDats
                                  where p.IsDeleted == false && p.IsActive == true && p.NhaDatId == nhaDatId
                                  orderby p.OrderDisplay
                                  select p;
                    List = varlist.ToList();
                }
                catch
                {
                    List = null;
                }
                finally
                {
                    db.Dispose();
                }
            }
            return List;
        }

        public static List<VG_NhaDat> GetList(string keyWord,int loaiNhaDatId, int NhaDatId, int ProvinId, int WardId, string strConnect)
        {
            List<VG_NhaDat> List = new List<VG_NhaDat>();

            using (ProvinceToLinQDataContext db = new ProvinceToLinQDataContext(strConnect))
            {
                try
                {

                    if (!string.IsNullOrEmpty(keyWord))
                    {
                        if (ProvinId > 0)
                        {
                            if (WardId > 0)
                            {
                                if (NhaDatId > 0)
                                {
                                    if (loaiNhaDatId > 0)
                                    {
                                        var varlist = from p in db.VG_NhaDats
                                                      where p.IsDeleted == false && p.IsActive == true && (p.Name == keyWord || p.Description == keyWord) && p.ProvinceId == ProvinId && p.WardId == WardId && p.NhaDatId == NhaDatId && p.LoaiNhaDatId == loaiNhaDatId
                                                      orderby p.OrderDisplay
                                                      select p;
                                        List = varlist.ToList();
                                    }
                                    else
                                    {
                                        var varlist = from p in db.VG_NhaDats
                                                      where p.IsDeleted == false && p.IsActive == true && (p.Name == keyWord || p.Description == keyWord) && p.ProvinceId == ProvinId && p.WardId == WardId && p.NhaDatId == NhaDatId
                                                      orderby p.OrderDisplay
                                                      select p;
                                        List = varlist.ToList();
                                    }
                                }
                                else
                                {
                                    var varlist = from p in db.VG_NhaDats
                                                  where p.IsDeleted == false && p.IsActive == true && (p.Name == keyWord || p.Description == keyWord) && p.ProvinceId == ProvinId && p.WardId == WardId
                                                  orderby p.OrderDisplay
                                                  select p;
                                    List = varlist.ToList();
                                }
                            }
                            else
                            {
                                var varlist = from p in db.VG_NhaDats
                                              where p.IsDeleted == false && p.IsActive == true && (p.Name == keyWord || p.Description == keyWord) && p.ProvinceId == ProvinId
                                              orderby p.OrderDisplay
                                              select p;
                                List = varlist.ToList();
                            }
                        }
                        else
                        {
                            var varlist = from p in db.VG_NhaDats
                                          where p.IsDeleted == false && p.IsActive == true && (p.Name == keyWord || p.Description == keyWord)
                                          orderby p.OrderDisplay
                                          select p;
                            List = varlist.ToList();
                        }
                        
                    }
                    else
                    {
                        if (ProvinId > 0)
                        {
                            if (WardId > 0)
                            {
                                if (NhaDatId > 0)
                                {
                                    if (loaiNhaDatId > 0)
                                    {
                                        var varlist = from p in db.VG_NhaDats
                                                      where p.IsDeleted == false && p.IsActive == true  && p.ProvinceId == ProvinId && p.WardId == WardId && p.NhaDatId == NhaDatId && p.LoaiNhaDatId == loaiNhaDatId
                                                      orderby p.OrderDisplay
                                                      select p;
                                        List = varlist.ToList();
                                    }
                                    else
                                    {
                                        var varlist = from p in db.VG_NhaDats
                                                      where p.IsDeleted == false && p.IsActive == true  && p.ProvinceId == ProvinId && p.WardId == WardId && p.NhaDatId == NhaDatId
                                                      orderby p.OrderDisplay
                                                      select p;
                                        List = varlist.ToList();
                                    }
                                }
                                else
                                {
                                    var varlist = from p in db.VG_NhaDats
                                                  where p.IsDeleted == false && p.IsActive == true  && p.ProvinceId == ProvinId && p.WardId == WardId
                                                  orderby p.OrderDisplay
                                                  select p;
                                    List = varlist.ToList();
                                }
                            }
                            else
                            {
                                var varlist = from p in db.VG_NhaDats
                                              where p.IsDeleted == false && p.IsActive == true  && p.ProvinceId == ProvinId
                                              orderby p.OrderDisplay
                                              select p;
                                List = varlist.ToList();
                            }
                        }
                        else
                        {
                            var varlist = from p in db.VG_NhaDats
                                          where p.IsDeleted == false && p.IsActive == true
                                          orderby p.OrderDisplay
                                          select p;
                            List = varlist.ToList();
                        }
                    }
                     
                    
                }
                catch
                {
                    List = null;
                }
                finally
                {
                    db.Dispose();
                }
            }
            return List;
        }

        public static List<VG_NhaDat> GetList(string keyWord, int loaiNhaDatId, int nhaDatId, int provinId, int wardId, string price, string area, string strConnect)
        {
            List<VG_NhaDat> List = new List<VG_NhaDat>();

            using (ProvinceToLinQDataContext db = new ProvinceToLinQDataContext(strConnect))
            {
                try
                {

                    //var varlist = db.SearchNhaDat("");
                    //List = varlist.ToList();
                    string sql = "select * from VG_NhaDat where 1=1";
                    if (!string.IsNullOrEmpty(keyWord))
                    {
                        sql += " and (name like N'%" + keyWord.Replace("'", "") + "%' or [Description] like N'%" + keyWord.Replace("'", "") + "%')";
                    }

                    if (loaiNhaDatId > 0)
                    {
                        sql += " and LoaiNhaDatId = " + loaiNhaDatId.ToString();
                    }
                    if (nhaDatId > 0)
                    {
                        sql += " and NhaDatId = " + nhaDatId.ToString();
                    }
                    if (provinId > 0)
                    {
                        sql += " and ProvinceId = " + provinId.ToString();
                    }
                    if (wardId > 0)
                    {
                        sql += " and WardId = " + wardId.ToString();
                    }

                    #region Giá
                    if (!string.IsNullOrEmpty(price))
                    {
                        string[] arr;
                        int priceBegin, priceEnd = 0;
                        string unitPrice = "triệu";

                        if (price.Contains("-"))
                        {
                            arr = price.Split('-');
                            if (arr.Count() > 0)
                            {
                                if (arr[0].Contains("triệu"))
                                {
                                    string temp1 = arr[0].Trim();
                                    temp1 = temp1.ToLower();
                                    temp1 = temp1.Replace("triệu", "").Trim();
                                    priceBegin = Convert.ToInt32(temp1);

                                    string temp = arr[1].Trim();
                                    temp = temp.ToLower();
                                    if (temp.Contains("triệu"))
                                    {
                                        temp = temp.Replace("triệu", "").Trim();
                                        unitPrice = "triệu";
                                    }
                                    if (temp.Contains("tỷ"))
                                    {
                                        temp = temp.Replace("tỷ", "").Trim();
                                        unitPrice = "tỷ";
                                    }
                                    if (temp.Contains("vnđ"))
                                    {
                                        temp = temp.Replace("vnđ", "").Trim();
                                        unitPrice = "vnđ";
                                    }
                                    if (temp.Contains("usd"))
                                    {
                                        temp = temp.Replace("usd", "").Trim();
                                        unitPrice = "usd";
                                    }
                                    priceEnd = Convert.ToInt32(temp);
                                    sql += " and price >= " + priceBegin.ToString() + " and price<=" + priceEnd.ToString() + " and PriceUnit like N'%" + unitPrice + "'%";

                                }
                                else
                                {

                                    priceBegin = Convert.ToInt32(arr[0].Trim());

                                    string temp = arr[1].Trim();
                                    temp = temp.ToLower();
                                    if (temp.Contains("triệu"))
                                    {
                                        temp = temp.Replace("triệu", "").Trim();
                                        unitPrice = "triệu";
                                    }
                                    if (temp.Contains("tỷ"))
                                    {
                                        temp = temp.Replace("tỷ", "").Trim();
                                        unitPrice = "tỷ";
                                    }
                                    if (temp.Contains("vnđ"))
                                    {
                                        temp = temp.Replace("vnđ", "").Trim();
                                        unitPrice = "vnđ";
                                    }
                                    if (temp.Contains("usd"))
                                    {
                                        temp = temp.Replace("usd", "").Trim();
                                        unitPrice = "usd";
                                    }
                                    priceEnd = Convert.ToInt32(temp);
                                    sql += " and price >= " + priceBegin.ToString() + " and price<=" + priceEnd.ToString() + " and PriceUnit like N'%" + unitPrice + "%'";
                                }
                            }
                        }
                        else
                            if (price.Contains(">"))
                            {
                                arr = price.Split('>');
                                if (arr.Count() > 0)
                                {

                                    string temp = arr[1].Trim();
                                    temp = temp.ToLower();
                                    if (temp.Contains("triệu"))
                                    {
                                        temp = temp.Replace("triệu", "").Trim();
                                        unitPrice = "triệu";
                                    }
                                    if (temp.Contains("tỷ"))
                                    {
                                        temp = temp.Replace("tỷ", "").Trim();
                                        unitPrice = "tỷ";
                                    }
                                    if (temp.Contains("vnđ"))
                                    {
                                        temp = temp.Replace("vnđ", "").Trim();
                                        unitPrice = "vnđ";
                                    }
                                    if (temp.Contains("usd"))
                                    {
                                        temp = temp.Replace("usd", "").Trim();
                                        unitPrice = "usd";
                                    }
                                    priceBegin = Convert.ToInt32(temp);
                                    priceEnd = Convert.ToInt32(temp);
                                    sql += " and price > " + priceEnd.ToString() + " and PriceUnit like N'%" + unitPrice + "%'";
                                }
                            }
                            else
                                if (price.Contains("<"))
                                {
                                    arr = price.Split('<');
                                    if (arr.Count() > 0)
                                    {

                                        string temp = arr[1].Trim();
                                        temp = temp.ToLower();
                                        if (temp.Contains("triệu"))
                                        {
                                            temp = temp.Replace("triệu", "").Trim();
                                            unitPrice = "triệu";
                                        }
                                        if (temp.Contains("tỷ"))
                                        {
                                            temp = temp.Replace("tỷ", "").Trim();
                                            unitPrice = "tỷ";
                                        }
                                        if (temp.Contains("vnđ"))
                                        {
                                            temp = temp.Replace("vnđ", "").Trim();
                                            unitPrice = "vnđ";
                                        }
                                        if (temp.Contains("usd"))
                                        {
                                            temp = temp.Replace("usd", "").Trim();
                                            unitPrice = "usd";
                                        }
                                        priceBegin = Convert.ToInt32(temp);
                                        priceEnd = Convert.ToInt32(temp);
                                        sql += " and price < " + priceBegin.ToString() + " and PriceUnit like N'%" + unitPrice + "%'";
                                    }
                                }
                    }
                    #endregion

                    #region Dien tich
                    if (!string.IsNullOrEmpty(area))
                    {
                        string[] arr;
                        int areaBegin, areaEnd = 0;
                        string unitArea = "m2";

                        if (price.Contains("-"))
                        {
                            arr = price.Split('-');
                            if (arr.Count() > 0)
                            {

                                areaBegin = Convert.ToInt32(arr[0].Trim());
                                string temp = arr[1].Trim();
                                temp = temp.ToLower();
                                if (temp.Contains("m2"))
                                {
                                    temp = temp.Replace("m2", "").Trim();
                                    unitArea = "m2";
                                }

                                areaEnd = Convert.ToInt32(temp);
                                sql += " and price >= " + areaBegin.ToString() + " and price<=" + areaEnd.ToString() + " and PriceUnit like N'%" + unitArea + "%'";
                            }
                        }
                        else
                            if (price.Contains(">"))
                            {
                                arr = price.Split('>');
                                if (arr.Count() > 0)
                                {

                                    string temp = arr[0].Trim();
                                    temp = temp.ToLower();
                                    if (temp.Contains("m2"))
                                    {
                                        temp = temp.Replace("m2", "").Trim();
                                        unitArea = "m2";
                                    }

                                    areaBegin = Convert.ToInt32(temp);
                                    areaEnd = Convert.ToInt32(temp);
                                    sql += " and price > " + areaEnd.ToString() + " and PriceUnit like N'%" + unitArea + "%'";
                                }
                            }
                            else
                                if (price.Contains("<"))
                                {
                                    arr = price.Split('<');
                                    if (arr.Count() > 0)
                                    {

                                        string temp = arr[0].Trim();
                                        temp = temp.ToLower();
                                        if (temp.Contains("m2"))
                                        {
                                            temp = temp.Replace("m2", "").Trim();
                                            unitArea = "m2";
                                        }

                                        areaBegin = Convert.ToInt32(temp);
                                        areaEnd = Convert.ToInt32(temp);
                                        sql += " and price < " + areaBegin.ToString() + " and PriceUnit like N'%" + unitArea + "%'";
                                    }
                                }
                    }
                    #endregion

                    var varlist = db.ExecuteQuery<VG_NhaDat>(sql);
                    List = varlist.ToList();

                }
                catch
                {
                    List = null;
                }
                finally
                {
                    db.Dispose();
                }
            }
            return List;
        }

        public static nlit_Place GetById(int id, string strConnect)
        {
            nlit_Place res = new nlit_Place();

            using (ProvinceToLinQDataContext db = new ProvinceToLinQDataContext(strConnect))
            {
                try
                {

                    res = db.nlit_Places.Single(p => p.Id == id);

                }
                catch
                {

                }
                finally
                {
                    db.Dispose();
                }
            }
            return res;
        }

        
        public static VG_NhaDat GetVGNhaDatById(int id, string strConnect)
        {
            VG_NhaDat res = new VG_NhaDat();

            using (ProvinceToLinQDataContext db = new ProvinceToLinQDataContext(strConnect))
            {
                try
                {

                    res = db.VG_NhaDats.Single(p => p.Id == id);

                }
                catch
                {

                }
                finally
                {
                    db.Dispose();
                }
            }
            return res;
        }
    
    }
}
